﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using BCCommon.Extensions;
using BCCommon.OAEnums;
using BCEntity.OA.Attendance.AttendanceCheckInRecord;
using BCCommon;

namespace BCData.OA.Attendance.AttendanceCheckInRecord
{
    public class AttendanceCheckInRecordData : IAttendanceCheckInRecordData
    {
        private const string QUERY = @"SELECT
                                        CheckInRecordId,
                                        AdminId,
                                        CompanyId,
                                        DepartmentId,
                                        AdminName,
                                        AttendanceGroupId,
                                        CheckInMode,
                                        CheckInType,
                                        Status,
                                        BaseCheckInTime,
                                        CheckInTime,
                                        Location,
                                        Longitude,
                                        Latitude,
                                        IsSync,
                                        Remark,
                                        WifiName,
                                        MAC,
                                        SourceType,
                                        RemarkImage,
                                        CreateTime,
                                        ModifierName,ModifierId,
                                        ModifyTime,ClassId 
                                        FROM
                                        OA_AttendanceCheckInRecord Where Status<>'Abandon' ";

        private readonly IDatabaseContext databaseContext;

        public AttendanceCheckInRecordData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        public Task<int> DeleteAsync(string adminName, long checkInRecordId)
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = SqlCommands.GetUpdateSql("OA_AttendanceCheckInRecord", new string[] { "Status", "ModifierName", "ModifyTime" }, new string[] { "CheckInRecordId" });
            parameters.Add("?OA_AttendanceCheckInRecord", checkInRecordId, DbType.Int64);
            parameters.Add("?Status", "Abandon", DbType.AnsiString, size: 50);
            parameters.Add("?ModifierName", adminName, DbType.AnsiString, size: 50);
            parameters.Add("?ModifyTime", DateTime.Now, DbType.DateTime);
            return this.databaseContext.Master.ExecuteScalarAsync<int>(sql, parameters, databaseContext.Transaction);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<AttendanceCheckInRecordEntity> InserAsync(AttendanceCheckInRecordEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("OA_AttendanceCheckInRecord", new string[] { "AdminId", "CompanyId", "DepartmentId", "AdminName", "AttendanceGroupId", "CheckInMode", "CheckInType", "Status", "BaseCheckInTime", "CheckInTime", "Location", "Longitude", "Latitude", "IsSync", "Remark", "RemarkImage", "WifiName", "MAC", "SourceType", "ClassId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", entity.AdminId, DbType.Int64);
            parameters.Add("?CompanyId", entity.CompanyId, DbType.Int32);
            parameters.Add("?DepartmentId", entity.DepartmentId, DbType.Int32);
            parameters.Add("?AdminName", entity.AdminName, DbType.AnsiString, size: 30);
            parameters.Add("?AttendanceGroupId", entity.AttendanceGroupId, DbType.Int64);
            parameters.Add("?CheckInMode", entity.CheckInMode, DbType.AnsiString, size: 50);
            parameters.Add("?CheckInType", entity.CheckInType, DbType.AnsiString, size: 50);
            parameters.Add("?BaseCheckInTime", entity.BaseCheckInTime, DbType.AnsiString, size: 50);
            parameters.Add("?CheckInTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?Location", entity.Location, DbType.AnsiString, size: 100);
            parameters.Add("?Longitude", entity.Longitude, DbType.Double);
            parameters.Add("?Latitude", entity.Latitude, DbType.Double);
            parameters.Add("?WifiName", entity.WifiName, DbType.AnsiString, size: 50);
            parameters.Add("?MAC", entity.MAC, DbType.AnsiString, size: 50);
            parameters.Add("?Remark", entity.Remark, DbType.AnsiString);
            parameters.Add("?RemarkImage", entity.RemarkImage, DbType.AnsiString);
            parameters.Add("?SourceType", entity.SourceType, DbType.AnsiString);
            parameters.Add("?Status", entity.Status, DbType.AnsiString, size: 50);
            parameters.Add("?IsSync", entity.IsSync, DbType.Boolean);
            parameters.Add("?ClassId", entity.ClassId, DbType.Int64);

            string querySql = string.Format(@"{0}{1} AND CheckInRecordId=LAST_INSERT_ID();", sql, QUERY);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<AttendanceCheckInRecordEntity>(querySql, parameters, databaseContext.Transaction);
        }

        public async Task<AttendanceCheckInRecordEntity> UpdateAsync(AttendanceCheckInRecordEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("OA_AttendanceCheckInRecord", new string[] { "ModifierId", "ModifyTime", "ModifierName", "Status", "CheckInTime", "Location", "Longitude", "Latitude", "Remark", "RemarkImage", "WifiName", "MAC" }, new string[] { "CheckInRecordId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ModifierId", entity.ModifierId, DbType.Int64);
            parameters.Add("?ModifyTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?ModifierName", entity.ModifierName, DbType.AnsiString, size: 50);
            parameters.Add("?CheckInTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?Location", entity.Location, DbType.AnsiString, size: 100);
            parameters.Add("?Longitude", entity.Longitude, DbType.Double);
            parameters.Add("?Latitude", entity.Latitude, DbType.Double);
            parameters.Add("?WifiName", entity.WifiName, DbType.AnsiString, size: 50);
            parameters.Add("?MAC", entity.MAC, DbType.AnsiString, size: 50);
            parameters.Add("?Remark", entity.Remark, DbType.AnsiString);
            parameters.Add("?RemarkImage", entity.RemarkImage, DbType.AnsiString);
            parameters.Add("?SourceType", entity.SourceType, DbType.AnsiString);
            parameters.Add("?Status", entity.Status, DbType.AnsiString, size: 50);
            parameters.Add("?CheckInRecordId", entity.CheckInRecordId, DbType.Int64);

            string querySql = string.Format(@"{0}{1} AND CheckInRecordId=?CheckInRecordId;", sql, QUERY);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<AttendanceCheckInRecordEntity>(querySql, parameters, databaseContext.Transaction);
        }

        /// <summary>
        /// 校验打卡记录是否存在
        /// </summary>
        /// <param name="checkInRecordId"></param>
        /// <returns></returns>
        public async Task<bool> CheckIsExists(long checkInRecordId)
        {
            string sql = @"SELECT 1 FROM OA_AttendanceCheckInRecord WHERE CheckInRecordId=?ClassId AND  Status<>'" + AttendanceBaseStatus.Abandon.ToString() + "' LIMIT 1";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?CheckInRecordId", checkInRecordId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameter) != null;
        }

        /// <summary>
        /// 获取指定员工所在日期内的打卡记录
        /// </summary>
        /// <param name="adminId">员工Id</param>
        /// <param name="date">指定日期</param>
        /// <returns></returns>
        public async Task<IEnumerable<AttendanceCheckInRecordEntity>> GetByAsync(long adminId, int departmentId, DateTime date)
        {
            string sql = " SELECT  CheckInRecordId,CheckInTime,BaseCheckInTime,Location,Longitude,Latitude,WifiName,MAC,Status,CompanyId,DepartmentId,AttendanceGroupId,ClassId FROM  OA_AttendanceCheckInRecord Where  Status<>'Abandon' AND AdminId=?AdminId AND DATE(CheckInTime)=?Date AND  DepartmentId=?DepartmentId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", adminId, DbType.Int64);
            parameters.Add("?DepartmentId", departmentId, DbType.Int32);
            parameters.Add("?Date", date, DbType.DateTime);
            return await this.databaseContext.Slave.QueryAsync<AttendanceCheckInRecordEntity>(sql, parameters);
        }

        /// <summary>
        /// 更新指定的打卡记录时间为最新时间
        /// </summary>
        /// <param name="checkInRecordId">指定的打卡记录</param>
        /// <param name="adminName">修改人</param>
        public async Task<AttendanceCheckInRecordEntity> UpdateCheckInTimeAsync(long checkInRecordId, string adminName, long modifierId)
        {
            string sql = SqlCommands.GetUpdateSql("OA_AttendanceCheckInRecord", new string[] { "CheckInTime", "ModifierId", "ModifierName", "ModifyTime" }, new string[] { "CheckInRecordId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CheckInRecordId", checkInRecordId, DbType.Int64);
            parameters.Add("?ModifierId", modifierId, DbType.Int64);
            parameters.Add("?ModifierName", adminName, DbType.AnsiString, size: 50);
            parameters.Add("?ModifyTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?CheckInTime", DateTime.Now, DbType.DateTime);
            string querySql = string.Format("{0} {1} AND CheckInRecordId=?CheckInRecordId", sql, QUERY);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<AttendanceCheckInRecordEntity>(querySql, parameters, databaseContext.Transaction);
        }

        public async Task<AttendanceCheckInRecordEntity> GetAsync(long checkInRecordId)
        {
            string sql = string.Format("{0} AND CheckInRecordId=?CheckInRecordId ", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CheckInRecordId", checkInRecordId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<AttendanceCheckInRecordEntity>(sql, parameters);
        }

        public async Task<AttendanceCheckInRecordEntity> GetIsExistsByTime(long adminId, long groupId, long classId, string baseCheckInTime)
        {
            string sql = string.Format("{0} AND AdminId=?AdminId AND DATE(CheckInTime)=?CheckInTime ", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", adminId, DbType.Int64);
            parameters.Add("?CheckInTime", DateTime.Now.Date, DbType.DateTime);
            if (baseCheckInTime.IsNotNull())
            {
                sql += "AND BaseCheckInTime=?BaseCheckInTime ";
                parameters.Add("?BaseCheckInTime", baseCheckInTime, DbType.AnsiString);
            }
            if (groupId > 0)
            {
                sql += "AND AttendanceGroupId=?AttendanceGroupId ";
                parameters.Add("?AttendanceGroupId", groupId, DbType.Int64);
            }
            if (classId > 0)
            {
                sql += "AND ClassId=?ClassId ";
                parameters.Add("?ClassId", classId, DbType.Int64);
            }
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<AttendanceCheckInRecordEntity>(sql, parameters);
        }

        /// <summary>
        /// 分页获取打卡记录信息
        /// </summary>
        /// <param name="companyId">公司Id</param>
        /// <param name="adminDto">当前登录管理员</param>
        /// <param name="pageIndex">分页页码</param>
        /// <param name="pageSize">分页页数</param>
        /// <param name="ascending">是否升序</param>
        /// <param name="sourceType">来源类型</param>
        /// <param name="status">打卡状态</param>
        /// <param name="checkInType">打卡方式</param>
        /// <param name="startTime">打卡开始时间</param>
        /// <param name="endTime">打卡结束时间</param>
        public async Task<(IEnumerable<AttendanceCheckInRecordEntity>, int)> GetListAsync(int companyId, long adminId, int pageIndex, int pageSize, bool? ascending, SourceType? sourceType, CheckInStatus? status, CheckInType? checkInType, DateTime? startTime = null, DateTime? endTime = null, int? departmentId = null)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            string QUERY_JOIN = @"SELECT
                                       A.CheckInRecordId,
                                       A.AdminId,
                                       A.CompanyId,
                                       A.DepartmentId,
                                       A.AdminName,
                                       A.AttendanceGroupId,
                                       A.CheckInMode,
                                       A.CheckInType,
                                       A.Status,
                                       A.BaseCheckInTime,
                                       A.CheckInTime,
                                       A.Location,
                                       A.Longitude,
                                       A.Latitude,
                                       A.IsSync,
                                       A.Remark,
                                       A.WifiName,
                                       A.MAC,
                                       A.SourceType,
                                       A.RemarkImage,
                                       A.CreateTime,
                                       A.ModifierId,
                                       A.ModifierName,
                                       A.ModifyTime,
                                        A.ClassId,B.GroupName,C.ClassName  
                                        FROM OA_AttendanceCheckInRecord A 
                                        Left JOIN OA_AttendanceGroup B  ON A.AttendanceGroupId=B.GroupId 
                                        Left JOIN OA_AttendanceClass C ON A.ClassId =C.ClassId ";

            string QUERY_JOIN_COUNT = @"SELECT COUNT(1) 
                                        FROM OA_AttendanceCheckInRecord A 
                                        Left JOIN OA_AttendanceGroup B  ON A.AttendanceGroupId=B.GroupId 
                                        Left JOIN OA_AttendanceClass C ON A.ClassId =C.ClassId ";
            if (companyId > 0)
            {
                wheres.Add(" A.CompanyId=?CompanyId");
                parameters.Add("?CompanyId", companyId, DbType.Int32);

            }
            if (checkInType.HasValue)
            {
                wheres.Add(" A.CheckInType = ?CheckInType");
                parameters.Add("?CheckInType", checkInType.Value.ToString(), DbType.AnsiString);
            }
            if (departmentId.HasValue)
            {
                wheres.Add(" A.DepartmentId = ?DepartmentId");
                parameters.Add("?DepartmentId", departmentId.Value, DbType.Int32);
            }
            if (status.HasValue)
            {
                wheres.Add(" A.Status = ?Status");
                parameters.Add("?Status", status.Value.ToString(), DbType.AnsiString);
            }
            if (sourceType.HasValue)
            {
                wheres.Add(" A.SourceType = ?SourceType");
                parameters.Add("?SourceType", sourceType.Value.ToString(), DbType.AnsiString);
            }
            if (adminId > 0)
            {
                wheres.Add(" A.AdminId = ?AdminId");
                parameters.Add("?AdminId", adminId, DbType.Int64);
            }
            if (startTime.HasValue)
            {
                wheres.Add(" A.CheckInTime >= ?StartTime");
                parameters.Add("?StartTime", startTime.Value.Date, DbType.DateTime);
            }
            if (endTime.HasValue)
            {
                wheres.Add(" A.CheckInTime <= ?EndTime");
                parameters.Add("?EndTime", endTime.Value.Date, DbType.DateTime);
            }

            string sql = string.Format(@"{0} {1} ORDER BY A.CreateTime {4} LIMIT {2},{3}; {5} {1}",
                        QUERY_JOIN,
                        wheres.Count > 0 ? "Where" + string.Join(" AND ", wheres) : "",
                       (pageIndex - 1) * pageSize,
                        pageSize,
                        ascending.HasValue && ascending.Value ? "ASC" : "DESC",
                        QUERY_JOIN_COUNT);

            return await this.databaseContext.Slave.QueryMultipleAsync<AttendanceCheckInRecordEntity,int>(sql, parameters);
        }
    }
}
